﻿CREATE PROCEDURE [dbo].[Report_CategorySameStoreComparison]
	  @RangeReviewId int
	, @CategoryId smallint	
	, @StoreId smallint
	, @StoreGroupId smallint
	, @Delist bit
AS



begin try drop table #salesTable; end try begin catch end catch;

declare @startDatePY DATE = 
	(
		select CASE WHEN HasPriorYear = 1 THEN DATEADD(Month, -12, FirstMonth) ELSE FirstMonth END
		from RangeReviews where Id = @RangeReviewId
	)
declare @startDate DATE = (select FirstMonth from RangeReviews where Id = @RangeReviewId)
declare @totalMonths INT = (select TotalMonths from RangeReviews where Id = @RangeReviewId)
declare @endDate DATE = DATEADD(Month, @totalMonths - 1, @startDate)
declare @endDatePY DATE = DATEADD(Month, @totalMonths - 1, @startDatePY)
declare @totalCoverage INT = 
(
	select 
		CASE 
			WHEN @StoreId <> 0 THEN 1 * @totalMonths
			WHEN @StoreGroupId <> 0 THEN (Select COUNT(StoreId) From StoreGroupMembers Where StoreGroupId = @StoreGroupId) * @totalMonths
			ELSE (Select Count(StoreId) from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0) * @totalMonths
		END
)

declare @catLevel TINYINT = (Select Level From Categories Where Id = @CategoryId)

SELECT
	rr.Title
	, CASE 
		WHEN @catLevel = 3 THEN cat.Id 
		ELSE cat.ParentId 
	  END AS CategoryId
	, CASE 
		WHEN @catLevel = 3 THEN cat.Name
		ELSE parentCat.Name
	  END AS Category
	, months.Date
	, ex.Rate ExchangeRate
	, ex.ForexRate RatePerf 
	, CASE WHEN umc.Id IS NOT NULL THEN SUM(Units / umc.Value) ELSE SUM(Units) END AS Units
	, CASE WHEN co.IsVATIncluded = 0 THEN SUM(Sales)  ELSE SUM(Sales / (1 + ar.TaxRate)) END AS Sales	
	, CASE WHEN am.Margin is not null THEN SUM(Sales) * am.Margin 
		   WHEN scm.Margin is not null THEN SUM(Sales) * scm.Margin 
		   WHEN cm.Margin is not null THEN SUM(Sales) * cm.Margin 
		   ELSE SUM(GM)END AS GM					
	, SUM(PIDiff) as PIDiff
	, SUM(ChargeOffs) as ChargeOffs
	, SUM(inv.Cost) as InventoryCost
	, ar.Id
	, CASE 
		WHEN months.Date BETWEEN @startDatePY AND DATEADD(MONTH, @totalMonths - 1, @startDatePY) THEN 1
		WHEN months.Date BETWEEN @startDate AND @endDate THEN 0
		ELSE NULL
	END AS IsPriorYear
	, ar.Delist
Into #salesTable
FROM Articles ar WITH (NOLOCK) 
	INNER JOIN Categories cat WITH (NOLOCK)  on ar.CategoryId = cat.Id
	INNER JOIN Categories parentCat WITH (NOLOCK)  on cat.ParentId = parentCat.Id and parentCat.ParentId = 6
	INNER JOIN RangeReviews rr WITH (NOLOCK)  on ar.RangeReviewId = rr.Id
	INNER JOIN Countries co WITH (NOLOCK)  on co.Id = rr.CountryId
	INNER JOIN Currencies cur WITH (NOLOCK)  on rr.CountryId = cur.CountryId
	INNER JOIN Months months WITH (NOLOCK)  on (months.Date BETWEEN @startDatePY AND @endDatePY OR months.Date BETWEEN @startDate AND @endDate)
	INNER JOIN Stores store WITH (NOLOCK)  on store.CountryId = co.Id
	LEFT JOIN ArticleSales sales WITH (NOLOCK)  on sales.ArticleId = ar.Id 
		AND sales.Date = months.Date 
		AND sales.Confirmed = 1
		AND sales.StoreId = store.Id
	LEFT JOIN Inventories inv WITH (NOLOCK)  on inv.ArticleId = ar.Id 
		AND inv.Date = months.Date 
		AND inv.StoreId = store.Id
	LEFT JOIN CurrencyExchangeRates ex WITH (NOLOCK)  on ex.Date = months.Date and ex.SourceCurrencyId = cur.Id and ex.TargetCurrencyId = rr.TargetCurrencyId
	LEFT JOIN UnitOfMeasureConvertions umc WITH (NOLOCK)  on ar.UnitOfMeasureConvertionId = umc.Id and ar.UOM = umc.SourceUnit
	LEFT JOIN TheoreticalMargins am WITH (NOLOCK) on am.CountryId = rr.CountryId AND  am.ArticleId = ar.Id  
	LEFT JOIN TheoreticalMargins scm WITH (NOLOCK) on scm.CountryId = rr.CountryId AND (scm.ArticleId is null AND scm.CategoryId = cat.Id) 
	LEFT JOIN TheoreticalMargins cm WITH (NOLOCK) on cm.CountryId = rr.CountryId AND (cm.ArticleId is null AND cm.CategoryId <> cat.Id AND cm.CategoryId = cat.ParentId)
WHERE
	ar.RangeReviewId = @RangeReviewId
	AND ar.IsDeletedPerformance = 0
	AND store.Id in (select StoreId from RangeReviewStores where RangeReviewId = @RangeReviewId and IsExcluded = 0)
	AND (@StoreId = 0 OR (@StoreId <> 0 AND store.Id = @StoreId))
	AND (@StoreGroupId = 0 OR (@StoreGroupId <> 0 AND store.Id IN (Select StoreId From StoreGroupMembers Where StoreGroupId = @StoreGroupId)))
GROUP BY rr.Title, cat.Id, cat.Name, cat.ParentId, parentCat.Name, ar.Id, ar.Name, ar.TaxRate, am.Margin, scm.Margin, cm.Margin, umc.Value, umc.TargetUnit, umc.Id, co.IsVATIncluded, months.Date, ex.Rate, ex.ForexRate, Delist
OPTION(RECOMPILE);

Select 
	CASE 
		WHEN @StoreId <> 0 THEN (Select Name From Stores Where Id = @StoreId) 
		WHEN @StoreGroupId <> 0 THEN (Select Name From StoreGroups Where Id = @StoreGroupId) 
		ELSE 'General'
	END AS Name 
	, Title
	, s.CategoryId
	, Category
	, SUM(s.Units) / CAST(@totalCoverage AS MONEY) as Units
	, SUM(s.Sales * isnull(s.RatePerf, s.ExchangeRate)) / @totalCoverage as Sales
	, SUM(s.GM * isnull(s.RatePerf, s.ExchangeRate)) / @totalCoverage as GM 
	, CAST(SUM(s.Units) AS MONEY) / Total.Units as UnitsShare
	, SUM(s.Sales) / Total.Sales as SalesShare
	, SUM(s.GM) / Total.GM as GMShare
	, SUM(s.PIDiff) / SUM(s.Sales) as PIDiff
	, SUM(s.ChargeOffs) / SUM(s.Sales) as ChargeOffs
	, SUM(s.InventoryCost * ISNULL(s.RatePerf, s.ExchangeRate)) / @totalCoverage as InventoryCost
	, Spaces.LinealSpace as LinearSpace
	, 0 SKUs --Esta info sale del Pareto
	, 0 Sales80 --Esta info sale del Pareto
	, s.IsPriorYear 
From #salesTable s
Inner Join 
(
	Select 
		SUM(Units) as Units, 
		SUM(Sales) as Sales, 
		SUM(GM) as GM,
		IsPriorYear
	From #salesTable
	Where IsPriorYear IS NOT NULL
	Group By IsPriorYear 
) Total on s.IsPriorYear = Total.IsPriorYear
Left Join 
(
	Select CategoryId, SUM(LinealSpace) LinealSpace From (
	select 
		CASE 
			WHEN @catLevel = 3 THEN cat.Id 
			ELSE parentCat.Id 
		  END AS CategoryId
		, SUM(Width * Facings) / 100 as LinealSpace
	from Articles ar WITH (NOLOCK) 
	Inner Join Categories cat WITH (NOLOCK) on ar.CategoryId = cat.Id
	Inner Join Categories parentCat WITH (NOLOCK) on parentCat.Id = cat.ParentId
	where (@CategoryId = 0 or (parentCat.Id = @CategoryId OR cat.Id = @CategoryId))
	  and ar.RangeReviewId = @RangeReviewId
	  and ar.IsDeletedPerformance = 0
	group by parentCat.Id, cat.Id) data
	group by CategoryId
) Spaces on Spaces.CategoryId = s.CategoryId

Where 
	(@CategoryId = 0 or s.CategoryId = @CategoryId) 
	AND s.IsPriorYear IS NOT NULL
	AND (@Delist is null OR s.Delist = @Delist)
Group by
	Title
	, s.CategoryId
	, Category
	, Total.Units
	, Total.Sales
	, Total.GM
	, Spaces.LinealSpace
	, s.IsPriorYear

OPTION(RECOMPILE);

DROP TABLE #salesTable

SET NOCOUNT OFF
	